package LawsonTransformJava.pgm;


//OracleTAEmpServDAO implementation of the 
//TAEmpServDAO interface. This class can contain all
//Oracle specific code and SQL statements. 
//The client is thus shielded from knowing 
//these implementation details.

import java.sql.*;
import java.text.SimpleDateFormat;
import java.util.*;
import java.util.Date;
import java.io.*;
import java.lang.reflect.InvocationTargetException;
import java.lang.reflect.Method;

import LawsonTransformJava.xref.XrefLPTranType;
import LawsonTransformJava.xref.XrefPersAction;
import LawsonTransformJava.xref.XrefPosition;

import LawsonTransformJava.dao.DAOFactoryAccessDB;
import LawsonTransformJava.dao.DAOFactoryOracleDB;
import LawsonTransformJava.dao.DAOTAEmpTrans;
import LawsonTransformJava.dao.DAObject;
import LawsonTransformJava.data.TblTAEmpTrans;

public class LP570 extends DAObject implements 
 DAOTAEmpTrans, Runnable {

	private XrefLPTranType xLPTranTypes = null; 
	private XrefPosition xPositions = null;
	
	 public LP570(File output) throws SQLException {
	 // initialization 
			try {
				this.output = new FileWriter(this.outputFile = output);
				init();
			} catch (IOException e) {
				// Could no write properties file
				System.out.println("IOException: " + e);
			}
	}
	
	private void init() throws IOException, SQLException {
		    // Set the data and xref variables
			data = DAOFactoryOracleDB.getDAOOracleDB(true);
		    xref = new DAOFactoryAccessDB();
		    
		    xLPTranTypes = new XrefLPTranType();
		    xPositions = new XrefPosition();
		    
		    sqlData = "SELECT * FROM " + data.getSchema() + "." + TABLE_NAME;
		    stmtData = data.getDataStatement();	    
		    countStmtData = data.getCountStatement();
		    //Set the Date Format for output
		    df = new SimpleDateFormat("MM/dd/yyyy HH:mm:ss");
	}
	
	public TblTAEmpTrans findTAEmpTrans() {
			try {
				String schemax=data.getSchema();
				TblTAEmpTrans tet = new TblTAEmpTrans();
				sqlData = "SELECT "      + 
						schemax + "." + TABLE_NAME  + ".R_PLAN, " +		
						schemax + "." + TABLE_NAME  + ".EMPLOYEE_GROUP, " +	
						schemax + "." + TABLE_NAME  + ".EMPLOYEE, " +
						schemax + "." + TABLE_NAME  + ".POSITION, " +
						schemax + "." + TABLE_NAME  + ".TA_HOURS, " +	
						schemax + "." + TABLE_NAME  + ".TA_TYPE, " +		
						schemax + "." + TABLE_NAME  + ".R_DATE, " +
						schemax + "." + TABLE_NAME  + ".TRAN_TYPE, " +	
					   	schemax + "." + TABLE_NAME  + ".DESCRIPTION, " +
					   	schemax + "." + TABLE_NAME2  + ".LST_PER_DT_HRS, " +
						schemax + "." + TABLE_NAME2  + ".LST_PER_DT_ERN " +
						   "FROM "        + schemax + "." + TABLE_NAME  +
						   " INNER JOIN " + schemax + "." + TABLE_NAME2 +
						   " ON "         + schemax + "." + TABLE_NAME  + ".COMPANY" +
						   "="	          + schemax + "." + TABLE_NAME2 + ".COMPANY" +
						   " AND "        + schemax + "." + TABLE_NAME  + ".EMPLOYEE" +
						   "="	          + schemax + "." + TABLE_NAME2 + ".EMPLOYEE" +
						   " AND "        + schemax + "." + TABLE_NAME  + ".R_PLAN" +
						   "="	          + schemax + "." + TABLE_NAME2 + ".R_PLAN" +
						   " AND "        + schemax + "." + TABLE_NAME  + ".EMPLOYEE_GROUP" +
						   "="	          + schemax + "." + TABLE_NAME2 + ".EMPLOYEE_GROUP" +
						   " AND "        + schemax + "." + TABLE_NAME  + ".POSITION" +
						   "="	          + schemax + "." + TABLE_NAME2 + ".POSITION" +
	 				       (whereClause==null?"":whereClause);
	
				rslt = data.getDataStatement().executeQuery(sqlData);
				rsmd = rslt.getMetaData();
				if (rslt.next())
					hasMoreRecs = true;
				else
					hasMoreRecs = false;
				tet = (TblTAEmpTrans) createObject(rslt, tet);
				return tet;
			} catch (SQLException e) {
			    // Could not connect to the database
				System.out.println("SQLException: " + e);
			} catch (InvocationTargetException ite) {
				System.out.println("InvocationException: " + ite);
			} catch (IllegalAccessException iae) {
				System.out.println("IllegalAccessException: " + iae);
			}
		  return null;
	}
	
	public TblTAEmpTrans findTADEmpTrans(String employeeNumber) {
		try {		
			String schemax=data.getSchema();
			TblTAEmpTrans tet = new TblTAEmpTrans();
			sqlData = "SELECT "      + 
					schemax + "." + TABLE_NAME  + ".R_PLAN, " +		
					schemax + "." + TABLE_NAME  + ".EMPLOYEE_GROUP, " +	
					schemax + "." + TABLE_NAME  + ".EMPLOYEE, " +
					schemax + "." + TABLE_NAME  + ".POSITION, " +
					schemax + "." + TABLE_NAME  + ".TA_HOURS, " +	
					schemax + "." + TABLE_NAME  + ".TA_TYPE, " +		
					schemax + "." + TABLE_NAME  + ".R_DATE, " +
					schemax + "." + TABLE_NAME  + ".TRAN_TYPE, " +	
				   	schemax + "." + TABLE_NAME  + ".DESCRIPTION, " +
				   	schemax + "." + TABLE_NAME2  + ".LST_PER_DT_HRS, " +
					schemax + "." + TABLE_NAME2  + ".LST_PER_DT_ERN " +
					   "FROM "        + schemax + "." + TABLE_NAME  +
					   " INNER JOIN " + schemax + "." + TABLE_NAME2 +
					   " ON "         + schemax + "." + TABLE_NAME  + ".COMPANY" +
					   "="	          + schemax + "." + TABLE_NAME2 + ".COMPANY" +
					   " AND "        + schemax + "." + TABLE_NAME  + ".EMPLOYEE" +
					   "="	          + schemax + "." + TABLE_NAME2 + ".EMPLOYEE" +
					   " AND "        + schemax + "." + TABLE_NAME  + ".R_PLAN" +
					   "="	          + schemax + "." + TABLE_NAME2 + ".R_PLAN" +
					   " AND "        + schemax + "." + TABLE_NAME  + ".EMPLOYEE_GROUP" +
					   "="	          + schemax + "." + TABLE_NAME2 + ".EMPLOYEE_GROUP" +
					   " AND "        + schemax + "." + TABLE_NAME  + ".POSITION" +
					   "="	          + schemax + "." + TABLE_NAME2 + ".POSITION" +
				       (whereClause==null?"":whereClause);
			rslt = data.getDataStatement().executeQuery(sqlData);
			rsmd = rslt.getMetaData();
			if (rslt.next())
				hasMoreRecs = true;
			else
				hasMoreRecs = false;
			tet = (TblTAEmpTrans) createObject(rslt, tet);
			return tet;
			//createFields(tae);
		} catch (SQLException e) {
		    // Could not connect to the database
			System.out.println("SQLException: " + e);
		} catch (InvocationTargetException ite) {
			System.out.println("InvocationException: " + ite);
		} catch (IllegalAccessException iae) {
			System.out.println("IllegalAccessException: " + iae);
		}
		return new TblTAEmpTrans();
	}
	
	public TblTAEmpTrans nextTAEmpTrans() {
			TblTAEmpTrans tet = new TblTAEmpTrans();
			try {
				if (rslt.next()) {
					hasMoreRecs = true;
					tet = (TblTAEmpTrans) createObject(rslt, tet);
				}
				else
					hasMoreRecs = false;
				return tet;
				} catch (SQLException e) {
			    // Could not connect to the database
				System.out.println("SQLException: " + e);
			} catch (InvocationTargetException ite) {
				System.out.println("InvocationException: " + ite);
			} catch (IllegalAccessException iae) {
				System.out.println("IllegalAccessException: " + iae);
			}
		  return null;
	}
	
	public boolean hasMore () {
		return hasMoreRecs;
	}
	
	public int countTAEmpTrans() {
		try {
			countRecs = data.getCountStatement().executeQuery("SELECT COUNT(*) FROM " + data.getSchema() + "." + TABLE_NAME + (whereClause==null?"":whereClause));
			countRecs.next();
			return recCount=countRecs.getInt(1);
		} catch (SQLException e) {
			// Could not connect to the database
			System.out.println("SQLException: " + e);
		}
		return 0;
	}
	
	public void setWhereClause(String where) {
		this.whereClause = where;
	}
	
	public void run() {
		TblTAEmpTrans tet = null;
	
		try {
			//Update current date/time
			Date now = new Date();
			System.out.println(outputFile.getName() + " Start " + df.format(now));
			//Write timestamp to output file
			output.write(outputFile.getName() + " Start " + df.format(now) + "\n");
	
			tet = findTAEmpTrans();
			output.write(writeLP570Layout(tet) + "\n");
			System.out.println(whereClause + " " + countTAEmpTrans());
			while (hasMore()) {
				tet = nextTAEmpTrans();
				output.write(writeLP570Layout(tet) + "\n");
			}
			//Update current date/time
			now = new Date();
			System.out.println(outputFile.getName() + " Finish " + df.format(now));
			//Write timestamp to output file
			output.write(outputFile.getName() + " Finish " + df.format(now) + "\n");
			output.flush();
			output.close();
		} catch (IOException e) {
			// Could not write properties file
			// Could not create output file
			System.out.println("IOException: " + e);
		}
	
	}
	
	public ResultSet selectTAEmpTransRS(String sql) {
		// implement search customers here using the
		// supplied criteria.
		// Return a RecordSet. 
		return null;
	}
	
	public Collection selectTAEmpTransTO(String sql) {
		// implement search customers here using the
		// supplied criteria.
		// Alternatively, implement to return a Collection 
		// of Transfer Objects.
		return null;
	}
	
	//Output the LP570 format
	public String writeLP570Layout(TblTAEmpTrans tet) {
		//		String lpTranType = xLPTranTypes.xlate(Integer.toString(tet.getTran_type()));
		//		if (XrefLPTranType.errFound())
		return (
				"1" + "," +
				tet.getR_plan() + "," +
				tet.getEmployee_group()  + "," +
				tet.getEmployee() + "," +
				xPositions.xlate(tet.getPosition()) + "," +
				tet.getTa_hours() + "," +
				tet.getTa_type() + "," +
				tet.getR_date() + "," +
				//The following line will pass back a String but should be a value between 01 and 91 or 
				// the LP_Error code
				xLPTranTypes.xlate(Integer.toString(tet.getTran_type())) + "," +
				tet.getDescription() + "," +
				tet.getLst_per_dt_hrs() + "," +
				tet.getLst_per_dt_ern()			
			);
	}
}